package com.kakame.codenews.front.article.dao;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.springframework.stereotype.Repository;

import com.kakame.codenews.base.constant.SystemConstant;
import com.kakame.codenews.base.util.DateUtil;
import com.kakame.codenews.base.util.SystemUtil;
import com.kakame.codenews.base.web.dao.JdbcBaseDao;
import com.kakame.codenews.base.web.entity.Page;
import com.kakame.codenews.base.web.fast.mapping.common.Article;
import com.kakame.codenews.base.web.fast.mapping.nexus.ArticleComment;
import com.kakame.codenews.base.web.fast.mapping.nexus.CommentComment;

/**
 * 前台文章数据层
 * @author 何家明 2018年7月4日 下午9:42:15
 */
@Repository
public class FrontArticleDao extends JdbcBaseDao {

	/**
	 * 新增文章
	 * @author 何家明 2018年7月4日 下午9:44:36
	 * @param article
	 */
	public int add(Article article) {
        String insertSql = "";
        insertSql += "INSERT INTO t_common_article ";
        insertSql += "(id, article_title, article_category, article_label, article_visible, article_content, article_status, article_collect_num, ";
        insertSql += "article_click_num, article_comment_num, status, create_time, verify_time, create_user_id) ";
        insertSql += "VALUES ";
        insertSql += "(:id, :articleTitle, :articleCategory, :articleLabel, :articleVisible, :articleContent, 'C', 0, ";
        insertSql += "0, 0, 'C', now(), now(), :createUserId) ";
        String id = SystemConstant.T_COMMON_ARTICLE + DateFormatUtils.format(DateUtil.getNow(), "yyyyMMddHHmmssSSS");
        article.setId(id);
//        insertMap.put("articleStatus", "S");
//        insertMap.put("status", "S");
        int rows = update(insertSql, article);
        return rows;
	}


	/**
	 * <pre>
	 * <b>desc:</b> 修改文章 
	 * <b>auth:</b> ex-hejm
	 * <b>date:</b> 2018年7月19日 下午3:36:43
	 * </pre>
	 * @param article
	 * @return
	 */
    public int update(Article article) {
        String updateSql = "";
        updateSql += "UPDATE t_common_article ";
        updateSql += "   SET article_title    = :articleTitle, ";
        updateSql += "       article_category = :articleCategory, ";
        updateSql += "       article_label    = :articleLabel, ";
        updateSql += "       article_visible  = :articleVisible, ";
        updateSql += "       article_content  = :articleContent, ";
        updateSql += "       update_user_id   = :updateUserId, ";
        updateSql += "       update_time      = now() ";
        updateSql += " WHERE id               = :id ";
        int rows = update(updateSql, article);
        return rows;
    }
	
	/**
	 * 文章分页
	 * @author 何家明 2018年7月4日 下午10:05:59
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public Page<Article> page(Integer pageNo, Integer pageSize) {
		Map<String, Object> paramMap = SystemUtil.getSOHashMap();
		int pageStart = (pageNo - 1) * pageSize;
		paramMap.put("pageStart", pageStart);
		paramMap.put("pageSize", pageSize);
		
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT id, article_title, article_category, article_visible, article_label, article_content, article_collect_num, article_click_num, ");
		sb.append("article_comment_num, verify_time, create_user_id ");
		sb.append("FROM t_common_article ");
		sb.append("WHERE article_status = 'C' AND status = 'C'");
		sb.append("ORDER BY create_time DESC ");
		
		List<Article> countList = list(sb.toString(), paramMap, Article.class);
		sb.append("LIMIT :pageStart, :pageSize ");
		
		List<Article> list = list(sb.toString(), paramMap, Article.class);
		Page<Article> page = new Page<Article>(list, pageNo, pageSize, countList.size());
		
		return page;
	}

	public Article get(String articleId) {
		Map<String, Object> paramMap = SystemUtil.getSOHashMap();
		paramMap.put("articleId", articleId);
		
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT id, article_title, article_category, article_visible, article_label, article_content, article_collect_num, article_click_num, ");
		sb.append("article_comment_num, verify_time, create_user_id ");
		sb.append("FROM t_common_article ");
		sb.append("WHERE id = :articleId");
		List<Article> list = list(sb.toString(), paramMap, Article.class);
		return list.get(0);
	}


    public int delete(String articleId) {
        String updateSql = "";
        updateSql += "UPDATE t_common_article ";
        updateSql += "   SET status           = 'D', ";
        updateSql += "       update_user_id   = create_user_id, ";
        updateSql += "       update_time      = now() ";
        updateSql += " WHERE id               = :id ";
        int rows = update(updateSql, Article.create().id(articleId).build());
        return rows;
    }


    public List<ArticleComment> listChildComments(String articleId) {
        Map<String, Object> paramMap = SystemUtil.getSOHashMap();
        paramMap.put("articleId", articleId);
        String sql = "";
        sql += "SELECT " + SystemConstant.SINGLE_TABLE_QUERY_ALL_COLUMN;
        sql += "  FROM t_nexus_article_comment nac ";
        sql += " WHERE nac.status = 'C' ";
        sql += "       AND EXISTS( ";
        sql += "               SELECT ncc.comment_id ";
        sql += "                 FROM t_nexus_comment_comment ncc ";
        sql += "                WHERE ncc.distance =- 1 AND ncc.comment_id = nac.id ";
        sql += "       ) ";
        sql += "       AND article_id = :articleId ";
        List<ArticleComment> list = list(sql, paramMap, ArticleComment.class);
        return list;
    }


    public List<ArticleComment> listCommentRedirectReply(String commentParentId) {
        Map<String, Object> paramMap = SystemUtil.getSOHashMap();
        paramMap.put("commentParentId", commentParentId);
        String sql = "";
        sql += "SELECT " + SystemConstant.SINGLE_TABLE_QUERY_ALL_COLUMN;
        sql += "  FROM t_nexus_article_comment nac ";
        sql += " INNER JOIN ( ";
        sql += "	       SELECT comment_id, distance ";
        sql += "		     FROM t_nexus_comment_comment ";
        sql += "		    WHERE distance = 1 and comment_parent_id = :commentParentId ";
        sql += "	   ) ncc ON nac.id = ncc.comment_id ";
        sql += " WHERE nac.status = 'C' ";
        sql += " ORDER BY nac.create_time DESC";
        List<ArticleComment> list = list(sql, paramMap, ArticleComment.class);
        return list;
    }


    /**
     * <pre>
     * <b>desc:</b> 获取自身及所有的父评论
     * <b>auth:</b> ex-hejm
     * <b>date:</b> 2018年8月2日 上午11:15:57
     * </pre>
     * @param commentId
     */
    public List<CommentComment> listParentComments(String commentId) {
        Map<String, Object> paramMap = SystemUtil.getSOHashMap();
        paramMap.put("commentId", commentId);
        String sql = "";
        sql += "SELECT e2.id comment_id, e2.create_user_id, rel.distance ";
        sql += "  FROM t_nexus_article_comment e1, ";
        sql += "       t_nexus_article_comment e2, ";
        sql += "       t_nexus_comment_comment rel ";
        sql += " WHERE e1.id = :commentId ";
        sql += "       AND rel.comment_id = e1.id ";
        sql += "       AND rel.distance >= 0 ";
        sql += "       AND e2.id = rel.comment_parent_id ";
        sql += "       AND e1.status = 'C' ";
        sql += "       AND e2.status = 'C' ";
        sql += "       AND rel.status = 'C' ";
        List<CommentComment> list = list(sql, paramMap, CommentComment.class);
        return list;
    }


    public void insertNexusArticleComment(ArticleComment dbAc) {
        super.insert(dbAc);
    }


    public void insertNexusCommentComment(List<CommentComment> commentReplys) {
        super.batchInsert(commentReplys);
    }


    public int updateCommentNum(String articleCommentId) {
        Map<String, Object> paramMap = SystemUtil.getSOHashMap();
        paramMap.put("articleCommentId", articleCommentId);
        String updateSql = "";
        updateSql += "UPDATE t_nexus_article_comment ";
        updateSql += "   SET comment_num = comment_num + 1  ";
        updateSql += " WHERE id IN ( ";
        updateSql += "        SELECT id FROM ( ";
        updateSql += "            SELECT e2.id ";
        updateSql += "              FROM t_nexus_article_comment e1, ";
        updateSql += "                   t_nexus_article_comment e2, ";
        updateSql += "                   t_nexus_comment_comment rel ";
        updateSql += "             WHERE e1.id = :articleCommentId ";
        updateSql += "                   AND rel.comment_id = e1.id ";
        updateSql += "                   AND rel.distance > 0 ";
        updateSql += "                   AND e2.id = rel.comment_parent_id ";
        updateSql += "                   AND e1.status = 'C' ";
        updateSql += "                   AND e2.status = 'C' ";
        updateSql += "                   AND rel.status = 'C' ";
        updateSql += "            ) tmp";
        updateSql += "        ) ";
        int rows = update(updateSql, paramMap);
        return rows;
    }
}
